package movie.ticketing.system.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;

public class SystemDB {
    public static final String URL = "jdbc:mysql://localhost:3306/";
    public static final String NAME = "movie_system";
    public static final String USER = "root";
    public static final String PASSWORD = "1379";

    // 获取数据库连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL + NAME, USER, PASSWORD);
    }

    // 初始化数据库
    public void initializeDatabase() throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement()) {
            // 删除已存在的数据库
            stmt.executeUpdate("DROP DATABASE IF EXISTS " + NAME);
            // 创建新数据库
            stmt.executeUpdate("CREATE DATABASE " + NAME);
            // 使用新数据库
            stmt.executeUpdate("USE " + NAME);

            // 创建用户表
            String createUserTable = "CREATE TABLE users ("
                    + "id INT AUTO_INCREMENT PRIMARY KEY,"
                    + "phone VARCHAR(20) UNIQUE NOT NULL,"
                    + "username VARCHAR(50) NOT NULL,"
                    + "password VARCHAR(50) NOT NULL,"
                    + "register_time DATETIME NOT NULL,"
                    + "purchase_records TEXT,"
                    + "last_login_time DATETIME"
                    + ");";
            stmt.executeUpdate(createUserTable);

            // 创建管理员表
            String createAdminTable = "CREATE TABLE administrators ("
                    + "id INT AUTO_INCREMENT PRIMARY KEY,"
                    + "username VARCHAR(50) UNIQUE NOT NULL,"
                    + "password VARCHAR(50) NOT NULL"
                    + ");";
            stmt.executeUpdate(createAdminTable);

            // 插入默认管理员
            String insertAdmin = "INSERT INTO administrators (username, password) VALUES ('admin', '123456');";
            stmt.executeUpdate(insertAdmin);

            // 创建电影信息表
            String createMovieTable = "CREATE TABLE movies ("
                    + "id INT AUTO_INCREMENT PRIMARY KEY,"
                    + "movie_name VARCHAR(100) NOT NULL,"
                    + "duration INT NOT NULL,"
                    + "movie_types VARCHAR(200) NOT NULL,"
                    + "production_company VARCHAR(100) NOT NULL,"
                    + "ticket_price DECIMAL(10, 2) NOT NULL"
                    + ");";
            stmt.executeUpdate(createMovieTable);

            // 创建放映厅信息表
            String createRoomTable = "CREATE TABLE rooms ("
                    + "id INT AUTO_INCREMENT PRIMARY KEY,"
                    + "room_number INT UNIQUE NOT NULL"
                    + ");";
            stmt.executeUpdate(createRoomTable);

            // 创建电影场次表
            String createScreeningTable = "CREATE TABLE screenings ("
                    + "id INT AUTO_INCREMENT PRIMARY KEY,"
                    + "movie_id INT NOT NULL,"
                    + "room_id INT NOT NULL,"
                    + "start_time DATETIME NOT NULL,"
                    + "end_time DATETIME NOT NULL,"
                    + "FOREIGN KEY (movie_id) REFERENCES movies(id),"
                    + "FOREIGN KEY (room_id) REFERENCES rooms(id)"
                    + ");";
            stmt.executeUpdate(createScreeningTable);
        }
    }

    public static class DatabaseInitializer {
        public static void initialize() {
            try {
                // 加载驱动
                Class.forName("com.mysql.cj.jdbc.Driver");

                // 连接到 MySQL 服务器（不指定数据库）
                String url = "jdbc:mysql://localhost:3306?useSSL=false&serverTimezone=UTC";
                Connection connection = DriverManager.getConnection(url, USER, PASSWORD);
                Statement statement = connection.createStatement();

                // 删除现有数据库（如果存在）
                String dropDatabaseQuery = "DROP DATABASE IF EXISTS movie_system";
                statement.executeUpdate(dropDatabaseQuery);

                // 创建新数据库
                String createDatabaseQuery = "CREATE DATABASE movie_system";
                statement.executeUpdate(createDatabaseQuery);

                // 连接到新创建的数据库
                url = "jdbc:mysql://localhost:3306/movie_system?useSSL=false&serverTimezone=UTC";
                connection = DriverManager.getConnection(url, USER, PASSWORD);
                statement = connection.createStatement();

                // 创建用户表
                String createUserTableQuery = "CREATE TABLE users ("
                        + "id INT AUTO_INCREMENT PRIMARY KEY,"
                        + "phone VARCHAR(20) UNIQUE NOT NULL,"
                        + "username VARCHAR(50) NOT NULL,"
                        + "password VARCHAR(50) NOT NULL,"
                        + "registration_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                        + "last_login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
                        + ");";
                statement.executeUpdate(createUserTableQuery);

                // 创建管理员表
                String createAdminTableQuery = "CREATE TABLE administrators ("
                        + "id INT AUTO_INCREMENT PRIMARY KEY,"
                        + "username VARCHAR(50) UNIQUE NOT NULL,"
                        + "password VARCHAR(50) NOT NULL"
                        + ");";
                statement.executeUpdate(createAdminTableQuery);

                // 插入默认管理员
                String insertAdminQuery = "INSERT INTO administrators (username, password) VALUES ('admin', '123456')";
                statement.executeUpdate(insertAdminQuery);

                // 创建电影信息表
                String createMovieTableQuery = "CREATE TABLE movies ("
                        + "id INT AUTO_INCREMENT PRIMARY KEY,"
                        + "movie_name VARCHAR(100) NOT NULL,"
                        + "duration INT NOT NULL,"
                        + "genres VARCHAR(200) NOT NULL,"
                        + "production_company VARCHAR(100) NOT NULL,"
                        + "price DECIMAL(10, 2) NOT NULL"
                        + ");";
                statement.executeUpdate(createMovieTableQuery);

                // 创建放映厅表
                String createRoomTableQuery = "CREATE TABLE rooms ("
                        + "id INT AUTO_INCREMENT PRIMARY KEY,"
                        + "room_name VARCHAR(50) NOT NULL"
                        + ");";
                statement.executeUpdate(createRoomTableQuery);

                // 创建电影场次表
                String createScreeningTableQuery = "CREATE TABLE screenings ("
                        + "id INT AUTO_INCREMENT PRIMARY KEY,"
                        + "movie_id INT NOT NULL,"
                        + "room_id INT NOT NULL,"
                        + "start_time DATETIME NOT NULL,"
                        + "end_time DATETIME NOT NULL,"
                        + "FOREIGN KEY (movie_id) REFERENCES movies(id),"
                        + "FOREIGN KEY (room_id) REFERENCES rooms(id)"
                        + ");";
                statement.executeUpdate(createScreeningTableQuery);

                // 创建购票记录表
                String createTicketTableQuery = "CREATE TABLE tickets ("
                        + "id INT AUTO_INCREMENT PRIMARY KEY,"
                        + "user_id INT NOT NULL,"
                        + "screening_id INT NOT NULL,"
                        + "purchase_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                        + "FOREIGN KEY (user_id) REFERENCES users(id),"
                        + "FOREIGN KEY (screening_id) REFERENCES screenings(id)"
                        + ");";
                statement.executeUpdate(createTicketTableQuery);

                System.out.println("Database initialized successfully.");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        public static void main(String[] args) {
            initialize();
        }
    }
}